package com.admin.qujiang.util;

import com.google.common.collect.Maps;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * Excel导入工具类
 * Created by ht on 2018/6/8.
 *
 * @author ht
 */
public class ExcelImportUtils {
    private static final Logger logger = LoggerFactory.getLogger(ExcelImportUtils.class);

    /**
     * 读取Excel
     *
     * @param fileName   文件名
     * @param importItem 导入项
     * @return List数据
     */
    public static List<Map> readExcel(String fileName, Map<String, String> importItem) {
        File file = new File(fileName);
        List<Map> dataList = null;
        try {
            InputStream fileInputStream = new FileInputStream(file);

            //根据版本选择创建Workbook的方式
            Workbook workbook = null;
            //根据文件名判断文件是2003版本还是2007版本
            if (ExcelImportUtils.isExcel2007(fileName)) {
                workbook = new XSSFWorkbook(fileInputStream);
            } else {
                workbook = new HSSFWorkbook(fileInputStream);
            }

            dataList = readExcelValue(workbook, fileName, importItem);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return dataList;
    }

    /**
     * 读取Excel
     *
     * @param file          文件
     * @param tempDirectory 临时目录
     * @param importItem    导入项
     * @return List数据
     */
    public static List<Map> readExcel(MultipartFile file, String tempDirectory, Map<String, String> importItem) {
        String fileName = file.getOriginalFilename();

        //创建一个目录 （它的路径名由当前 File 对象指定，包括任一必须的父路径。）
        File uploadDir = new File(tempDirectory);
        if (!uploadDir.exists()) {
            uploadDir.mkdirs();
            logger.info("创建目录" + tempDirectory);
        }

        boolean isExcel2007 = ExcelImportUtils.isExcel2007(fileName);
        //excel后缀
        String suffix = isExcel2007 ? ".xlsx" : "xls";
        //新建一个文件
        File tempFile = new File(tempDirectory + "\\" + System.currentTimeMillis() + suffix);
        InputStream fileInputStream;

        List<Map> dataList = null;
        try {
            file.transferTo(tempFile);
            fileInputStream = new FileInputStream(tempFile);

            //根据版本选择创建Workbook的方式
            Workbook workbook;
            //根据文件名判断文件是2003版本还是2007版本
            if (isExcel2007) {
                workbook = new XSSFWorkbook(fileInputStream);
            } else {
                workbook = new HSSFWorkbook(fileInputStream);
            }

            dataList = readExcelValue(workbook, fileName, importItem);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return dataList;
    }

    /**
     * 读取Excel
     *
     * @param file          文件
     * @param tempDirectory 临时目录
     * @param importItem    导入项
     * @return List数据
     */
    public static List<Map> readExcel2(MultipartFile file, String tempDirectory, Map<String, String> importItem) throws IOException {
        String fileName = file.getOriginalFilename();

        //创建一个目录 （它的路径名由当前 File 对象指定，包括任一必须的父路径。）
        File uploadDir = new File(tempDirectory);
        if (!uploadDir.exists()) {
            uploadDir.mkdirs();
            logger.info("创建目录" + tempDirectory);
        }

        boolean isExcel2007 = ExcelImportUtils.isExcel2007(fileName);
        //excel后缀
        String suffix = isExcel2007 ? ".xlsx" : "xls";
        //新建一个文件
        File tempFile = new File(tempDirectory + "\\" + System.currentTimeMillis() + suffix);
        InputStream fileInputStream;

        List<Map> dataList = null;
        file.transferTo(tempFile);
        fileInputStream = new FileInputStream(tempFile);

        //根据版本选择创建Workbook的方式
        Workbook workbook;
        //根据文件名判断文件是2003版本还是2007版本
        if (isExcel2007) {
            workbook = new XSSFWorkbook(fileInputStream);
        } else {
            workbook = new HSSFWorkbook(fileInputStream);
        }

        dataList = readExcelValue2(workbook, fileName, importItem);

        return dataList;
    }

    /**
     * 解析Excel里面的数据
     *
     * @param workbook   Excel数据
     * @param fileName
     * @param importItem 定义好需要导入的列名称
     * @return Excel里面的数据
     */
    private static List<Map> readExcelValue(Workbook workbook, String fileName, Map<String, String> importItem) {
        //数据集
        List<Map> dataList = new ArrayList<>();
        Map rowDataMap = null;
        //得到第一个shell
        Sheet sheet = workbook.getSheetAt(0);
        //得到Excel的行数
        int totalRows = sheet.getPhysicalNumberOfRows();
        //excel总列数
        int totalCells = 0;
        //得到Excel的列数(前提是有行数)，从第一行算起
        if (totalRows >= 2 && sheet.getRow(1) != null) {
            totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
            if (totalCells < importItem.size()) {
                throw new CheckException("导入项少于" + importItem.size() + "个，请检查");
            } else if (totalCells > importItem.size()) {
                throw new CheckException("导入项大于" + importItem.size() + "个，请检查");
            }
        }

        String[] headLine = new String[totalCells];
        Row firstRow = sheet.getRow(0);
        Map<Integer, Boolean> hasDataMap = Maps.newHashMapWithExpectedSize(totalCells);
        for (int c = 0; c < totalCells; c++) {
            Cell cell = firstRow.getCell(c);
            //当前标题栏单元格的值
            String cellValue = cell.getStringCellValue();
            //如果导入的标题栏 能和定义好需要导入的列名称对应上，则导入对应列的数据
            if (importItem.get(cellValue) != null) {
                headLine[c] = importItem.get(cellValue);
                hasDataMap.put(c, true);
            } else {
                hasDataMap.put(c, false);
            }
        }

        //循环Excel行数,从第二行开始。标题不入库
        for (int r = 1; r < totalRows; r++) {
            Row row = sheet.getRow(r);
            if (row == null) {
                throw new CheckException("文件《" + fileName + "》 第" + (r + 1) + "行数据有问题，请仔细检查；");
            }
            rowDataMap = Maps.newHashMapWithExpectedSize(totalCells);

            //循环Excel的列
            for (int c = 0; c < totalCells; c++) {
                if (hasDataMap.get(c)) {
                    Cell cell = row.getCell(c);
                    String cellValue = "";
                    //转换数据
                    cellValue = conversionExcelDataFormat(cellValue, cell);

                    cellValue = StringUtils.isBlank(cellValue) ? null : cellValue;

                    rowDataMap.put(headLine[c], cellValue);
                }
            }

            dataList.add(rowDataMap);
        }

        return dataList;
    }
    /**
     * 解析Excel里面的数据
     *
     * @param workbook   Excel数据
     * @param fileName
     * @param importItem 定义好需要导入的列名称
     * @return Excel里面的数据
     */
    private static List<Map> readExcelValue2(Workbook workbook, String fileName, Map<String, String> importItem) {
        //数据集
        List<Map> dataList = new ArrayList<>();
        Map rowDataMap = null;
        //得到第一个shell
        Sheet sheet = workbook.getSheetAt(0);
        //得到Excel的行数
        int totalRows = sheet.getPhysicalNumberOfRows();
        //excel总列数
        int totalCells = 0;
        //得到Excel的列数(前提是有行数)，从第一行算起
        if (totalRows >= 2 && sheet.getRow(1) != null) {
            totalCells = sheet.getRow(0).getPhysicalNumberOfCells();

        }

        String[] headLine = new String[totalCells];
        Row firstRow = sheet.getRow(0);
        Map<Integer, Boolean> hasDataMap = Maps.newHashMapWithExpectedSize(totalCells);
        for (int c = 0; c < totalCells; c++) {
            Cell cell = firstRow.getCell(c);
            //当前标题栏单元格的值
            String cellValue = cell.getStringCellValue();
            //如果导入的标题栏 能和定义好需要导入的列名称对应上，则导入对应列的数据
            if (importItem.get(cellValue) != null) {
                headLine[c] = importItem.get(cellValue);
                hasDataMap.put(c, true);
            } else {
                hasDataMap.put(c, false);
            }
        }

        //循环Excel行数,从第二行开始。标题不入库
        for (int r = 1; r < totalRows; r++) {
            Row row = sheet.getRow(r);
            if (row == null) {
                throw new CheckException("文件《" + fileName + "》 第" + (r + 1) + "行数据有问题，请仔细检查；");
            }
            rowDataMap = Maps.newHashMapWithExpectedSize(totalCells);

            //循环Excel的列
            for (int c = 0; c < totalCells; c++) {
                if (hasDataMap.get(c)) {
                    Cell cell = row.getCell(c);
                    String cellValue = "";
                    //转换数据
                    cellValue = conversionExcelDataFormat(cellValue, cell);

                    cellValue = StringUtils.isBlank(cellValue) ? null : cellValue;

                    rowDataMap.put(headLine[c], cellValue);
                }
            }

            dataList.add(rowDataMap);
        }

        return dataList;
    }

    /**
     * 是否是2003的excel，返回true是2003
     *
     * @param filePath 文件地址
     * @return 返回true是2003
     */
    public static boolean isExcel2003(String filePath) {
        return filePath.matches("^.+\\.(?i)(xls)$");
    }

    /**
     * 是否是2007的excel，返回true是2007
     *
     * @param filePath 文件地址
     * @return 返回true是2007
     */
    public static boolean isExcel2007(String filePath) {
        return filePath.matches("^.+\\.(?i)(xlsx)$");
    }

    /**
     * 是否是2007的excel，返回true是2007
     *
     * @param cellValue 转换的数据
     * @param cell      excel数据
     * @return 返回true是2007
     */
    private static String conversionExcelDataFormat(String cellValue, Cell cell) {
        if (cell != null) {
            switch (cell.getCellType()) {
                case HSSFCell.CELL_TYPE_STRING:
                    cellValue = cell.getStringCellValue();
                    break;
                case HSSFCell.CELL_TYPE_NUMERIC:
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        Date date = cell.getDateCellValue();
                        cellValue = date == null ? null : TimeFormatUtils.dateToStr(date);
                    } else {
                        cellValue = DataUtils.formatNumberByDigits(cell.getNumericCellValue(), 6);
                    }
                    break;
                case HSSFCell.CELL_TYPE_FORMULA:
                    // 导入时如果为公式生成的数据则无值
                    if (!"".equals(cell.getStringCellValue())) {
                        cellValue = cell.getStringCellValue();
                    } else {
                        cellValue = cell.getNumericCellValue() + "";
                    }
                    break;
                case HSSFCell.CELL_TYPE_BLANK:
                    cellValue = null;
                    break;
                case HSSFCell.CELL_TYPE_ERROR:
                    cellValue = null;
                    break;
                case HSSFCell.CELL_TYPE_BOOLEAN:
                    cellValue = (cell.getBooleanCellValue() ? "1" : "0");
                    break;
                default:
                    cellValue = null;
            }

            //throw new CheckException("文件《" + fileName + "》 第" + (r + 1) + "列数据有问题，请仔细检查；");
        }

        return cellValue;
    }

    /**
     * 验证EXCEL文件
     *
     * @param filePath 文件地址
     * @return 返回true是EXCEL文件
     */
    public static boolean validateExcel(String filePath) {
        return filePath != null && (isExcel2003(filePath) || isExcel2007(filePath));
    }

    /**
     * 初步校验文件是否正常
     *
     * @param file Excel
     * @return 如果符合，则返回文件名
     */
    public static String checkFile(MultipartFile file) {
        if (file == null) {
            throw new CheckException("文件为空，上传失败");
        }

        //获取文件名
        String fileName = file.getOriginalFilename();
        if (StringUtils.isBlank(fileName) || file.getSize() == 0) {
            throw new CheckException("文件读取异常，上传失败");
        }

        if (!ExcelImportUtils.validateExcel(fileName)) {
            throw new CheckException("文件不是Excel，无法处理");
        }
        return fileName;
    }

}
